HiveSql计算占比、同比、占环比

一、数据准备

-- 创建表并插入数据
CREATE TABLE `saleorder`  (
  `order_id` int ,
  `order_time` date ,
  `order_num` int
) 

INSERT INTO `saleorder` VALUES 
(1, '2020-04-20', 420),
(2, '2020-04-04', 800),
(3, '2020-03-28', 500),
(4, '2020-03-13', 100),
(5, '2020-02-27', 300),
(6, '2020-01-07', 450),
(7, '2019-04-07', 800),
(8, '2019-03-15', 1200),
(9, '2019-02-17', 200),
(10, '2019-02-07', 600),
(11, '2019-01-13', 300);

查看表信息

select * from saleorder;

二、占比

写法一：

基本思路：用式内连接，外加嵌套找出分子分母，相除（最后要分组）

SELECT
	order_month,
	num,
	total,
	round( num / total, 2 ) AS ratio 
FROM
(
	-- 月统计
    SELECT 
	    DATE_FORMAT(order_time,"yyyy-MM") AS order_month,
	    sum( order_num ) AS num
    FROM    
	saleorder 
    GROUP BY
		DATE_FORMAT(order_time,"yyyy-MM")
)t1,
(
	-- 年统计
    SELECT 
	    year(order_time) AS order_year,
	    sum( order_num ) AS total
    FROM 
	saleorder 
    GROUP BY
		year(order_time)
)t2 
where substr(t1.order_month,1,4) = t2.order_year;

写法二：显示内连接

基本思路：显示内联接，先